package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import Model.Score;
import Util.ConnectionUtil;

public class ScoreDao {
	/*
	 * 查询成绩列表
	 */
	public static ArrayList<Score> scoreList() {
		ArrayList<Score> scoreList = new ArrayList<>();

		Connection conn = ConnectionUtil.getConnection();
		Statement stmt = null;
		try {
			// 执行查询
			System.out.println(" 实例化Statement对象...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT * FROM score";
			ResultSet rs = stmt.executeQuery(sql);
			// 展开结果集数据库
			while (rs.next()) {
				// 通过字段检索
				int scoreid = rs.getInt("scoreid");
				String name = rs.getString("name");
				int id = rs.getInt("id");
				String Coursename = rs.getString("Coursename");
				int Courseid = rs.getInt("Courseid");
				int classid = rs.getInt("classid");
				int score = rs.getInt("score");
				Score scores = new Score(scoreid, name, id, Coursename, Courseid, classid, score);
				scoreList.add(scores);
			}
		} catch (SQLException se) {
			// ���� JDBC ����
			se.printStackTrace();
		} catch (Exception e) {
			// ���� Class.forName ����
			e.printStackTrace();
		}
		System.out.println("查询成功!");
		return scoreList;

	}

	public static void addUsePrepareStatement(Score scoreinfo) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "insert into score values(?,?,?,?,?,?,?)";
		try {
			PreparedStatement psta = conn.prepareStatement(sql);
			psta.setInt(1, scoreinfo.getScoreid());
			psta.setString(2, scoreinfo.getName());
			psta.setInt(3, scoreinfo.getId());
			psta.setString(4, scoreinfo.getCoursename());
			psta.setInt(5, scoreinfo.getCourseid());
			psta.setInt(6, scoreinfo.getClassid());
			psta.setInt(7, scoreinfo.getScore());

			int result = psta.executeUpdate();
			if (result > 0) {
				System.out.println("插入成功");
			} else {
				System.out.println("插入失败");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		}
	}

	public static void del(String name, String coursename) {
		Connection conn = ConnectionUtil.getConnection();

		String sql = "delete from score where name = '" + name + "' and Coursename = '" + coursename + "' ;";

		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if (result > 0) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void update(String name, String coursename, int column, String value) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = null;
		switch (column) {
		case 1:
			sql = "update score SET name = '" + value + "'  where name = '" + name + "' and Coursename = '" + coursename
					+ "' ";
			break;

		case 2:
			sql = "update score SET id = '" + value + "'  where name = '" + name + "' and Coursename = '" + coursename
					+ "' ";
			break;

		case 3:
			sql = "update score SET Coursename = '" + value + "'  where name = '" + name + "' and Coursename = '"
					+ coursename + "' ";
			break;

		case 4:
			sql = "update score SET Courseid = '" + value + "'  where name = '" + name + "' and Coursename = '"
					+ coursename + "' ";
			break;

		case 5:
			sql = "update score SET classid = '" + value + "'  where name = '" + name + "' and Coursename = '"
					+ coursename + "' ";
			break;

		case 6:
			sql = "update score SET Score = '" + value + "'  where name = '" + name + "' and Coursename = '"
					+ coursename + "' ";
			break;

		default:
			break;
		}

		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if (result > 0) {
				System.out.println("更新成功");
			} else {
				System.out.println("更新失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void add(int scoreid, String name, int id, String Coursename, int Courseid, int classid, int Score) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "insert into score VALUES (? ,?, ?, ?, ?, ?, ?)";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, scoreid);
			ps.setString(2, name);
			ps.setInt(3, id);
			ps.setString(4, Coursename);
			ps.setInt(5, Courseid);
			ps.setInt(6, classid);
			ps.setInt(7, Score);
			int result = ps.executeUpdate();
			if (result > 0) {
				System.out.println("选课成功");
			} else {
				System.out.println("选课失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static ArrayList<Model.Score> getScoreList(int stuid) {
		ArrayList<Score> scoreList = new ArrayList<>();
		Connection connection = ConnectionUtil.getConnection();
		String sql = "select * from score where score.id= ? ";
		try {
			PreparedStatement psta = connection.prepareStatement(sql);
			psta.setInt(1, stuid);
			ResultSet rs = psta.executeQuery();
			while (rs.next()) {
				int scoreid = rs.getInt(1);
				String name = rs.getString(2);
				int id = rs.getInt(3);
				String Coursename = rs.getString(4);
				int Coureid = rs.getInt(5);
				int classid = rs.getInt(6);
				int score = rs.getInt(7);

				Score scores = new Score(scoreid, name, id, Coursename, Coureid, classid, score);
				scoreList.add(scores);
			}

			psta.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return scoreList;
	}

	public static ArrayList<Score> order(String coursename) {
		// select name,Coursename,Score from score where Coursename = ? ORDER BY
		// Score DESC
		ArrayList<Score> scoreList = new ArrayList<>();

		Connection conn = ConnectionUtil.getConnection();
		Statement stmt = null;
		try {
			// 执行查询
			System.out.println(" 实例化Statement对象...");
			stmt = conn.createStatement();
			String sql;
			sql = "select name,Coursename,Score from score where Coursename = '" + coursename + "' ORDER BY Score DESC";
			ResultSet rs = stmt.executeQuery(sql);
			// 展开结果集数据库
			while (rs.next()) {
				// 通过字段检索
				String name = rs.getString("name");
				String Coursename = rs.getString("Coursename");
				int score = rs.getInt("score");
				Score scores = new Score(0, name, 0, Coursename, 0, 0, score);
				scoreList.add(scores);
			}
		} catch (SQLException se) {
			// ���� JDBC ����
			se.printStackTrace();
		} catch (Exception e) {
			// ���� Class.forName ����
			e.printStackTrace();
		}
		System.out.println("查询成功!");
		return scoreList;
	}

	public static List<Map<String, Object>> getDetail() {
		List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
		Connection conn = ConnectionUtil.getConnection();
		Statement stmt = null;
		try {
			// 执行查询
			System.out.println(" 实例化Statement对象...");
			stmt = conn.createStatement();
			String sql = "SELECT Coursename,Max(Score) AS maxScore, MIN(Score) AS minScore, FORMAT(avg(Score), 2) AS avgScore,(select count(Score) from score where Score<60) AS failCount FROM score group by Coursename";
			ResultSet rs = stmt.executeQuery(sql);
			// 展开结果集数据库
			while (rs.next()) {
				// 通过字段检索
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("Coursename", rs.getString("Coursename"));
				map.put("maxScore", rs.getInt("maxScore"));
				map.put("minScore", rs.getInt("minScore"));
				map.put("avgScore", rs.getDouble("avgScore"));
				map.put("failCount", rs.getInt("failCount"));
				mapList.add(map);
			}
		} catch (SQLException se) {
			// ���� JDBC ����
			se.printStackTrace();
		} catch (Exception e) {
			// ���� Class.forName ����
			e.printStackTrace();
		}
		System.out.println("查询成功!");
		return mapList;
	}

}